{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入OpenAI库，并创建OpenAI客户端\n",
    "from dotenv import load_dotenv\n",
    "load_dotenv()\n",
    "from openai import OpenAI\n",
    "client = OpenAI()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>track_name</th>\n",
       "      <th>artist(s)_name</th>\n",
       "      <th>artist_count</th>\n",
       "      <th>released_year</th>\n",
       "      <th>released_month</th>\n",
       "      <th>released_day</th>\n",
       "      <th>in_spotify_playlists</th>\n",
       "      <th>in_spotify_charts</th>\n",
       "      <th>streams</th>\n",
       "      <th>in_apple_playlists</th>\n",
       "      <th>...</th>\n",
       "      <th>bpm</th>\n",
       "      <th>key</th>\n",
       "      <th>mode</th>\n",
       "      <th>danceability_%</th>\n",
       "      <th>valence_%</th>\n",
       "      <th>energy_%</th>\n",
       "      <th>acousticness_%</th>\n",
       "      <th>instrumentalness_%</th>\n",
       "      <th>liveness_%</th>\n",
       "      <th>speechiness_%</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Seven (feat. Latto) (Explicit Ver.)</td>\n",
       "      <td>Latto, Jung Kook</td>\n",
       "      <td>2</td>\n",
       "      <td>2023</td>\n",
       "      <td>7</td>\n",
       "      <td>14</td>\n",
       "      <td>553</td>\n",
       "      <td>147</td>\n",
       "      <td>141381703</td>\n",
       "      <td>43</td>\n",
       "      <td>...</td>\n",
       "      <td>125</td>\n",
       "      <td>B</td>\n",
       "      <td>Major</td>\n",
       "      <td>80</td>\n",
       "      <td>89</td>\n",
       "      <td>83</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LALA</td>\n",
       "      <td>Myke Towers</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>3</td>\n",
       "      <td>23</td>\n",
       "      <td>1474</td>\n",
       "      <td>48</td>\n",
       "      <td>133716286</td>\n",
       "      <td>48</td>\n",
       "      <td>...</td>\n",
       "      <td>92</td>\n",
       "      <td>C#</td>\n",
       "      <td>Major</td>\n",
       "      <td>71</td>\n",
       "      <td>61</td>\n",
       "      <td>74</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>vampire</td>\n",
       "      <td>Olivia Rodrigo</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>6</td>\n",
       "      <td>30</td>\n",
       "      <td>1397</td>\n",
       "      <td>113</td>\n",
       "      <td>140003974</td>\n",
       "      <td>94</td>\n",
       "      <td>...</td>\n",
       "      <td>138</td>\n",
       "      <td>F</td>\n",
       "      <td>Major</td>\n",
       "      <td>51</td>\n",
       "      <td>32</td>\n",
       "      <td>53</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Cruel Summer</td>\n",
       "      <td>Taylor Swift</td>\n",
       "      <td>1</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>7858</td>\n",
       "      <td>100</td>\n",
       "      <td>800840817</td>\n",
       "      <td>116</td>\n",
       "      <td>...</td>\n",
       "      <td>170</td>\n",
       "      <td>A</td>\n",
       "      <td>Major</td>\n",
       "      <td>55</td>\n",
       "      <td>58</td>\n",
       "      <td>72</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>WHERE SHE GOES</td>\n",
       "      <td>Bad Bunny</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>3133</td>\n",
       "      <td>50</td>\n",
       "      <td>303236322</td>\n",
       "      <td>84</td>\n",
       "      <td>...</td>\n",
       "      <td>144</td>\n",
       "      <td>A</td>\n",
       "      <td>Minor</td>\n",
       "      <td>65</td>\n",
       "      <td>23</td>\n",
       "      <td>80</td>\n",
       "      <td>14</td>\n",
       "      <td>63</td>\n",
       "      <td>11</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            track_name    artist(s)_name  artist_count  \\\n",
       "0  Seven (feat. Latto) (Explicit Ver.)  Latto, Jung Kook             2   \n",
       "1                                 LALA       Myke Towers             1   \n",
       "2                              vampire    Olivia Rodrigo             1   \n",
       "3                         Cruel Summer      Taylor Swift             1   \n",
       "4                       WHERE SHE GOES         Bad Bunny             1   \n",
       "\n",
       "   released_year  released_month  released_day  in_spotify_playlists  \\\n",
       "0           2023               7            14                   553   \n",
       "1           2023               3            23                  1474   \n",
       "2           2023               6            30                  1397   \n",
       "3           2019               8            23                  7858   \n",
       "4           2023               5            18                  3133   \n",
       "\n",
       "   in_spotify_charts    streams  in_apple_playlists  ...  bpm  key   mode  \\\n",
       "0                147  141381703                  43  ...  125    B  Major   \n",
       "1                 48  133716286                  48  ...   92   C#  Major   \n",
       "2                113  140003974                  94  ...  138    F  Major   \n",
       "3                100  800840817                 116  ...  170    A  Major   \n",
       "4                 50  303236322                  84  ...  144    A  Minor   \n",
       "\n",
       "  danceability_%  valence_% energy_% acousticness_%  instrumentalness_%  \\\n",
       "0             80         89       83             31                   0   \n",
       "1             71         61       74              7                   0   \n",
       "2             51         32       53             17                   0   \n",
       "3             55         58       72             11                   0   \n",
       "4             65         23       80             14                  63   \n",
       "\n",
       "   liveness_%  speechiness_%  \n",
       "0           8              4  \n",
       "1          10              4  \n",
       "2          31              6  \n",
       "3          11             15  \n",
       "4          11              6  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 导入数据文件\n",
    "import pandas as pd\n",
    "file_path = 'Spotify_Songs.csv'\n",
    "sales_data = pd.read_csv(file_path, nrows=20)\n",
    "sales_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Assistant(id='asst_frwRB2Ugjj2XVUGnRy5f3no7', created_at=1717210909, description=None, instructions='作为一名数据科学助理，当给定数据和一个查询时，你能编写适当的代码并创建适当的可视化。', metadata={}, model='gpt-4-0125-preview', name=None, object='assistant', tools=[CodeInterpreterTool(type='code_interpreter')], tool_resources=ToolResources(code_interpreter=ToolResourcesCodeInterpreter(file_ids=['file-TgNxU9yP74IectmQt2ScOYhY']), file_search=None), top_p=1.0, temperature=1.0, response_format='auto')\n"
     ]
    }
   ],
   "source": [
    "# 创建文件\n",
    "file = client.files.create(\n",
    "  file=open(file_path, \"rb\"),\n",
    "  purpose='assistants',\n",
    ")\n",
    "\n",
    "# 创建一个包含这个文件的助手\n",
    "assistant = client.beta.assistants.create(\n",
    "  instructions=\"作为一名数据科学助理，当给定数据和一个查询时，你能编写适当的代码并创建适当的可视化。\",\n",
    "  model=\"gpt-4-0125-preview\",\n",
    "  tools=[\n",
    "    {\"type\": \"code_interpreter\"}\n",
    "  ],\n",
    "  tool_resources={\n",
    "    \"code_interpreter\": {\n",
    "      \"file_ids\": [file.id]  # Here we add the file id\n",
    "    }\n",
    "  }\n",
    ")\n",
    "print(assistant)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Thread(id='thread_iIvaQeLDBTSUa6Zacil8KQ80', created_at=1717223262, metadata={}, object='thread', tool_resources=ToolResources(code_interpreter=ToolResourcesCodeInterpreter(file_ids=['file-TgNxU9yP74IectmQt2ScOYhY']), file_search=None))\n"
     ]
    }
   ],
   "source": [
    "# 创建对话线程并运行\n",
    "thread = client.beta.threads.create(\n",
    "  messages=[\n",
    "    {\n",
    "      \"role\": \"user\",\n",
    "      \"content\": \"根据数据集的内容，选择最有代表性的字段，根据畅销音乐的特点和趋势做一张可视化表格，每个音乐家用不同的颜色表示。\",\n",
    "      \"attachments\": [\n",
    "        {\n",
    "          \"file_id\": file.id,\n",
    "          \"tools\": [\n",
    "            { \"type\": \"code_interpreter\" }\n",
    "          ]\n",
    "        }\n",
    "      ]\n",
    "    }\n",
    "  ]\n",
    ")\n",
    "print(thread)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Run(id='run_EDjLmAUQ6PHRxgKHCUNkBk1h', assistant_id='asst_frwRB2Ugjj2XVUGnRy5f3no7', cancelled_at=None, completed_at=None, created_at=1717223264, expires_at=1717223864, failed_at=None, incomplete_details=None, instructions='作为一名数据科学助理，当给定数据和一个查询时，你能编写适当的代码并创建适当的可视化。', last_error=None, max_completion_tokens=None, max_prompt_tokens=None, metadata={}, model='gpt-4-0125-preview', object='thread.run', required_action=None, response_format='auto', started_at=None, status='queued', thread_id='thread_iIvaQeLDBTSUa6Zacil8KQ80', tool_choice='auto', tools=[CodeInterpreterTool(type='code_interpreter')], truncation_strategy=TruncationStrategy(type='auto', last_messages=None), usage=None, temperature=1.0, top_p=1.0, tool_resources={})\n"
     ]
    }
   ],
   "source": [
    "# 创建Run来运行和助手的对话\n",
    "run = client.beta.threads.runs.create(\n",
    "    thread_id=thread.id,\n",
    "    assistant_id=assistant.id,\n",
    ")\n",
    "print(run)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "您的Assistant正在努力做图表呢...\n",
      "您的Assistant正在努力做图表呢...\n",
      "您的Assistant正在努力做图表呢...\n",
      "当前Message: TextContentBlock(text=Text(annotations=[], value='根据数据集的内容，我们有以下字段可以用来表示畅销音乐的特点和趀势：\\n\\n- `track_name`: 音轨名称\\n- `artist(s)_name`: 艺术家名称\\n- `artist_count`: 艺术家数量\\n- `released_year`: 发布年份\\n- `streams`: 流量\\n- `bpm` (Beats Per Minute): 节拍速度\\n- `key`: 调性\\n- `mode`: 调式\\n- `danceability_%`: 可跳舞性百分比\\n- `valence_%`: 歌曲情感的积极性百分比\\n- `energy_%`: 能量百分比\\n- `acousticness_%`: 声学性百分比\\n- `instrumentalness_%`: 乐器音乐性百分比\\n- `liveness_%`: 现场感百分比\\n- `speechiness_%`: 语音性百分比\\n\\n考虑到要根据畅销音乐的特点和趋势进行可视化，同时也要每个音乐家以不同的颜色表示，我们可以选择几个关键特征如`streams` (流量)、`danceability_%` (可跳舞性)和`energy_%`（能量）来进行可视化，并以`released_year`为X轴，以展现趋势。\\n\\n接下来，我将会创建一张图表，展示不同艺术家的歌曲流量、可跳舞性和能量随时间的变化趋势。由于艺术家数量可能较多，为了可视化的清晰度，我们可能需要限制显示的艺术家数量。现在让我们先检查一下有多少位不同的艺术家，并确定一个合理的显示方法。'), type='text')\n",
      "您的Assistant正在努力做图表呢...\n",
      "当前Message: TextContentBlock(text=Text(annotations=[], value='数据集包含645位不同的艺术家，这意呩着如果我们尝试将所有艺术家的数据都展示在一张图表上，图表可能会非常混乱且难以阅读。因此，为了创建一个清晰且易于解读的可视化，我们将选择几位流量最高的艺术家进行展示。\\n\\n具体步骤如下：\\n1. 确定流量最高的几位艺术家。\\n2. 对这些艺术家的数据进行可视化，使用不同的颜色区分不同的艺术家，并展示随时间变化的趋势。\\n\\n现在，让我们先确定前10位流量最高的艺术家。'), type='text')\n",
      "您的Assistant正在努力做图表呢...\n",
      "您的Assistant正在努力做图表呢...\n",
      "您的Assistant正在努力做图表呢...\n",
      "当前Message: TextContentBlock(text=Text(annotations=[], value='选择了流量最高的10首歌曲进行分析和可视化时，我们注意到一首歌曲\"Love Grows (Where My Rosemary Goes)\"的流量数据似乎受到了异常值的影响，并且它的发布年份为1970，这可能是一个占位或错误的值。因此，在接下来的可视化中，我们将排除这首歌曲。\\n\\n我们将基于以下字段进行可视化：\\n- `track_name`：歌曲名称\\n- `artist(s)_name`：艺术家名称\\n- `released_year`：发布年份\\n- `streams`：流量\\n- `danceability_%`：可跳舞性百分比\\n- `energy_%`：能量百分比\\n\\n每首歌曲将使用一个不同的颜色表示。由于原始数据的一些问题，我们需要进行适当的调整和清理。现在，我将进行这些步骤，并创建一张展示这些畅销歌曲随发布年份变化的可跳舞性和能量的趋势图。'), type='text')\n",
      "您的Assistant正在努力做图表呢...\n",
      "当前Message: TextContentBlock(text=Text(annotations=[], value='似乎在尝试清理数据和准备可视化时出现了一个语法错误。我会立即更正这个问题并重新尝试绘制图表。'), type='text')\n",
      "您的Assistant正在努力做图表呢...\n",
      "您的Assistant正在努力做图表呢...\n",
      "当前Message: TextContentBlock(text=Text(annotations=[], value='我在尝试运行可视化代码时未能成功导入必要的可视化库`matplotlib`和`seaborn`。我会立即纠正这个问题并重试创建图表。让我们重新导入这些库，并绘制关于畅销歌曲的跳舞性随发布年份的变化图表。'), type='text')\n",
      "您的Assistant正在努力做图表呢...\n",
      "当前Message: TextContentBlock(text=Text(annotations=[], value='我在尝试运行可视化代码时未能成功导入必要的可视化库`matplotlib`和`seaborn`。我会立即纠正这个问题并重试创建图表。让我们重新导入这些库，并绘制关于畅销歌曲的跳舞性随发布年份的变化图表。'), type='text')\n",
      "图表已创建！\n",
      "当前Message: ImageFileContentBlock(image_file=ImageFile(file_id='file-03gX6k5HtwwlBQg6M75cRLe2'), type='image_file')\n"
     ]
    }
   ],
   "source": [
    "# 检查并等待可视化完成\n",
    "import time\n",
    "while True:\n",
    "    messages = client.beta.threads.messages.list(thread_id=thread.id)\n",
    "    try:\n",
    "        # 检查是否创建了图像\n",
    "        messages.data[0].content[0].image_file\n",
    "        # 等待运行完成\n",
    "        time.sleep(5)\n",
    "        print('图表已创建！')\n",
    "        if messages.data and messages.data[0].content:\n",
    "            print('当前Message:', messages.data[0].content[0])\n",
    "        break\n",
    "    except:\n",
    "        time.sleep(10)\n",
    "        print('您的Assistant正在努力做图表呢...')\n",
    "        if messages.data and messages.data[0].content:\n",
    "            print('当前Message:', messages.data[0].content[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 转换输出文件为PNG格式\n",
    "def convert_file_to_png(file_id, write_path):\n",
    "    data = client.files.content(file_id)\n",
    "    data_bytes = data.read()\n",
    "    with open(write_path, \"wb\") as file:\n",
    "        file.write(data_bytes)\n",
    "\n",
    "plot_file_id = messages.data[0].content[0].image_file.file_id\n",
    "image_path = \"音乐趋势图.png\"\n",
    "convert_file_to_png(plot_file_id,image_path)\n",
    "\n",
    "# 上传图表\n",
    "plot_file = client.files.create(\n",
    "  file=open(image_path, \"rb\"),\n",
    "  purpose='assistants'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[ImageFileContentBlock(image_file=ImageFile(file_id='file-03gX6k5HtwwlBQg6M75cRLe2'), type='image_file'),\n",
       " TextContentBlock(text=Text(annotations=[], value='我在尝试运行可视化代码时未能成功导入必要的可视化库`matplotlib`和`seaborn`。我会立即纠正这个问题并重试创建图表。让我们重新导入这些库，并绘制关于畅销歌曲的跳舞性随发布年份的变化图表。'), type='text'),\n",
       " TextContentBlock(text=Text(annotations=[], value='似乎在尝试清理数据和准备可视化时出现了一个语法错误。我会立即更正这个问题并重新尝试绘制图表。'), type='text'),\n",
       " TextContentBlock(text=Text(annotations=[], value='选择了流量最高的10首歌曲进行分析和可视化时，我们注意到一首歌曲\"Love Grows (Where My Rosemary Goes)\"的流量数据似乎受到了异常值的影响，并且它的发布年份为1970，这可能是一个占位或错误的值。因此，在接下来的可视化中，我们将排除这首歌曲。\\n\\n我们将基于以下字段进行可视化：\\n- `track_name`：歌曲名称\\n- `artist(s)_name`：艺术家名称\\n- `released_year`：发布年份\\n- `streams`：流量\\n- `danceability_%`：可跳舞性百分比\\n- `energy_%`：能量百分比\\n\\n每首歌曲将使用一个不同的颜色表示。由于原始数据的一些问题，我们需要进行适当的调整和清理。现在，我将进行这些步骤，并创建一张展示这些畅销歌曲随发布年份变化的可跳舞性和能量的趋势图。'), type='text'),\n",
       " TextContentBlock(text=Text(annotations=[], value='在选择前10位流量最高的艺术家时，出现了一些意外的结果，这可能是由于原始数据或处理过程中的错误。似乎有一些艺术家的名称被错误地整合成了其歌曲的特征描述或串连了多个数值，从而导致了异常的艺术家名称。\\n\\n为了绕过这个问题，我们可以改变策略：选择数据中流量（streams）最高的几首歌曲来可视化，而不是基于艺术家。这样，我们便可以避开艺术家名称可能引起的错误，并专注于展现畅销音乐的特点和趋势。\\n\\n我将首先确定流量最高的10首歌曲，然后对这些歌曲的流量、可跳舞性（`danceability_%`）和能量（`energy_%`）随发布年份的变化趋势进行可视化，每首歌曲将使用不同的颜色表示。让我们开始这个新的策略。'), type='text'),\n",
       " TextContentBlock(text=Text(annotations=[], value='数据集包含645位不同的艺术家，这意呩着如果我们尝试将所有艺术家的数据都展示在一张图表上，图表可能会非常混乱且难以阅读。因此，为了创建一个清晰且易于解读的可视化，我们将选择几位流量最高的艺术家进行展示。\\n\\n具体步骤如下：\\n1. 确定流量最高的几位艺术家。\\n2. 对这些艺术家的数据进行可视化，使用不同的颜色区分不同的艺术家，并展示随时间变化的趋势。\\n\\n现在，让我们先确定前10位流量最高的艺术家。'), type='text'),\n",
       " TextContentBlock(text=Text(annotations=[], value='根据数据集的内容，我们有以下字段可以用来表示畅销音乐的特点和趀势：\\n\\n- `track_name`: 音轨名称\\n- `artist(s)_name`: 艺术家名称\\n- `artist_count`: 艺术家数量\\n- `released_year`: 发布年份\\n- `streams`: 流量\\n- `bpm` (Beats Per Minute): 节拍速度\\n- `key`: 调性\\n- `mode`: 调式\\n- `danceability_%`: 可跳舞性百分比\\n- `valence_%`: 歌曲情感的积极性百分比\\n- `energy_%`: 能量百分比\\n- `acousticness_%`: 声学性百分比\\n- `instrumentalness_%`: 乐器音乐性百分比\\n- `liveness_%`: 现场感百分比\\n- `speechiness_%`: 语音性百分比\\n\\n考虑到要根据畅销音乐的特点和趋势进行可视化，同时也要每个音乐家以不同的颜色表示，我们可以选择几个关键特征如`streams` (流量)、`danceability_%` (可跳舞性)和`energy_%`（能量）来进行可视化，并以`released_year`为X轴，以展现趋势。\\n\\n接下来，我将会创建一张图表，展示不同艺术家的歌曲流量、可跳舞性和能量随时间的变化趋势。由于艺术家数量可能较多，为了可视化的清晰度，我们可能需要限制显示的艺术家数量。现在让我们先检查一下有多少位不同的艺术家，并确定一个合理的显示方法。'), type='text'),\n",
       " TextContentBlock(text=Text(annotations=[], value='首先，我将打开和检查上传的文件，以了解其内容和结构。这将帮助我明确哪些字段可以最好地代表畅销音乐的特点和趋势。接着，我将基于这些信息选择一个合适的可视化方法。让我们开始吧。'), type='text'),\n",
       " TextContentBlock(text=Text(annotations=[], value='根据数据集的内容，选择最有代表性的字段，根据畅销音乐的特点和趋势做一张可视化表格，每个音乐家用不同的颜色表示。'), type='text')]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 看看助手整个的思维和行动过程\n",
    "messages = client.beta.threads.messages.list(thread_id=thread.id)\n",
    "[message.content[0] for message in messages.data]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 定义提交用户的消息的函数\n",
    "def submit_message_wait_completion(assistant_id, thread, user_message, file_ids=None):\n",
    "    # 检查并等待活跃的Run完成\n",
    "    for run in client.beta.threads.runs.list(thread_id=thread.id).data:\n",
    "        if run.status == 'in_progress':\n",
    "            print(f\"等待Run {run.id} 完成...\")\n",
    "            while True:\n",
    "                run_status = client.beta.threads.runs.retrieve(thread_id=thread.id, run_id=run.id).status\n",
    "                if run_status in ['succeeded', 'failed']:\n",
    "                    break\n",
    "                time.sleep(5)  # 等待5秒后再次检查状态\n",
    "\n",
    "    # 提交消息\n",
    "    params = {\n",
    "        'thread_id': thread.id,\n",
    "        'role': 'user',\n",
    "        'content': user_message,\n",
    "    }\n",
    "    # 设置attachments\n",
    "    if file_ids:        \n",
    "        attachments = [{\"file_id\": file_id, \"tools\": [ {\"type\": \"code_interpreter\"}]} for file_id in file_ids]\n",
    "        params['attachments'] = attachments\n",
    "    client.beta.threads.messages.create(**params)\n",
    "\n",
    "    # 创建Run\n",
    "    run = client.beta.threads.runs.create(thread_id=thread.id, assistant_id=assistant_id)\n",
    "    return run \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Run(id='run_WdgdIFOyQXTM9ATigdrKgham', assistant_id='asst_frwRB2Ugjj2XVUGnRy5f3no7', cancelled_at=None, completed_at=None, created_at=1717223943, expires_at=1717224543, failed_at=None, incomplete_details=None, instructions='作为一名数据科学助理，当给定数据和一个查询时，你能编写适当的代码并创建适当的可视化。', last_error=None, max_completion_tokens=None, max_prompt_tokens=None, metadata={}, model='gpt-4-0125-preview', object='thread.run', required_action=None, response_format='auto', started_at=None, status='queued', thread_id='thread_iIvaQeLDBTSUa6Zacil8KQ80', tool_choice='auto', tools=[CodeInterpreterTool(type='code_interpreter')], truncation_strategy=TruncationStrategy(type='auto', last_messages=None), usage=None, temperature=1.0, top_p=1.0, tool_resources={})"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 使用submit_message函数, 来发送请求，让助手生成洞察\n",
    "submit_message_wait_completion(assistant.id, thread, \"请根据你刚才创建的图表，给我两个约20字的句子，描述最重要的洞察。这将用于幻灯片展示，揭示出数据背后的'秘密'。\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1. \"2019至2023年间，畅销歌曲的跳舞性明显上升，展现了流行音乐向更加活跃风格的转变。\"\n",
      "2. \"Taylor Swift和Duncan Laurence的歌曲以超高流量领跑，彰显了其在全球音乐市场的巨大影响力。\"\n"
     ]
    }
   ],
   "source": [
    "# 获取对话线程的响应\n",
    "def get_response(thread):\n",
    "    return client.beta.threads.messages.list(thread_id=thread.id)\n",
    "\n",
    "# 等待响应并打印洞察\n",
    "time.sleep(10) # 假设助理需要一些时间来生成洞察\n",
    "response = get_response(thread)\n",
    "bullet_points = response.data[0].content[0].text.value\n",
    "print(bullet_points)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Run(id='run_Kerm8qIBzG3zbRWkc79n0TN7', assistant_id='asst_frwRB2Ugjj2XVUGnRy5f3no7', cancelled_at=None, completed_at=None, created_at=1717224082, expires_at=1717224682, failed_at=None, incomplete_details=None, instructions='作为一名数据科学助理，当给定数据和一个查询时，你能编写适当的代码并创建适当的可视化。', last_error=None, max_completion_tokens=None, max_prompt_tokens=None, metadata={}, model='gpt-4-0125-preview', object='thread.run', required_action=None, response_format='auto', started_at=None, status='queued', thread_id='thread_iIvaQeLDBTSUa6Zacil8KQ80', tool_choice='auto', tools=[CodeInterpreterTool(type='code_interpreter')], truncation_strategy=TruncationStrategy(type='auto', last_messages=None), usage=None, temperature=1.0, top_p=1.0, tool_resources={})"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 根据见解生成标题 \n",
    "submit_message_wait_completion(assistant.id, thread, \"根据你创建的情节和要点，为幻灯片想一个非常简短的标题。它应该只反映你得出的主要见解。\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\"流行音乐趋势：活跃风格与巨星影响力\"\n"
     ]
    }
   ],
   "source": [
    "# 等待响应并打印标题\n",
    "time.sleep(10) # 等待助理生成标题\n",
    "response = get_response(thread)\n",
    "title = response.data[0].content[0].text.value\n",
    "print(title)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 提供我们公司的说明\n",
    "company_summary = \"我们是CD批发商，但是我们董事长也是个流行歌手！\"\n",
    "\n",
    "# 使用DALL-E 3生成图片\n",
    "response = client.images.generate(\n",
    "  model='dall-e-3',\n",
    "  prompt=f\"根据这个公司概述 {company_summary}, \\\n",
    "           创建一张展示成长和前进道路的启发性照片。这将用于季度销售规划会议\",\n",
    "       size=\"1024x1024\",\n",
    "       quality=\"hd\",\n",
    "       n=1\n",
    ")\n",
    "image_url = response.data[0].url\n",
    "\n",
    "# 获取DALL-E 3生成的图片\n",
    "import requests\n",
    "dalle_img_path = '音乐趋势图.png'\n",
    "img = requests.get(image_url)\n",
    "\n",
    "# 将图片保存到本地\n",
    "with open(dalle_img_path,'wb') as file:\n",
    "  file.write(img.content)\n",
    "\n",
    "# 上传图片提供给助手做为PPT素材\n",
    "dalle_file = client.files.create(\n",
    "  file=open(dalle_img_path, \"rb\"),\n",
    "  purpose='assistants'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "您的Assistant正在努力制作幻灯片...\n",
      "您的Assistant正在努力制作幻灯片...\n",
      "您的Assistant正在努力制作幻灯片...\n",
      "您的Assistant正在努力制作幻灯片...\n",
      "您的Assistant正在努力制作幻灯片...\n",
      "成功检索到 pptx_id: file-c39pmCGSIhl2llr1835lTMgP\n"
     ]
    }
   ],
   "source": [
    "title_template = \"\"\"\n",
    "from pptx import Presentation\n",
    "from pptx.util import Inches, Pt\n",
    "from pptx.enum.text import PP_PARAGRAPH_ALIGNMENT\n",
    "from pptx.dml.color import RGBColor\n",
    "\n",
    "# 创建新的演示文稿对象\n",
    "prs = Presentation()\n",
    "\n",
    "# 添加一个空白的幻灯片布局\n",
    "blank_slide_layout = prs.slide_layouts[6]\n",
    "slide = prs.slides.add_slide(blank_slide_layout)\n",
    "\n",
    "# 将幻灯片的背景颜色设置为黑色\n",
    "background = slide.background\n",
    "fill = background.fill\n",
    "fill.solid()\n",
    "fill.fore_color.rgb = RGBColor(0, 0, 0)\n",
    "\n",
    "# 在幻灯片左侧添加图片，上下留有边距\n",
    "left = Inches(0)\n",
    "top = Inches(0)\n",
    "height = prs.slide_height\n",
    "width = prs.slide_width * 3/5\n",
    "pic = slide.shapes.add_picture(image_path, left, top, width=width, height=height)\n",
    "\n",
    "# 在较高位置添加标题文本框\n",
    "left = prs.slide_width * 3/5\n",
    "top = Inches(2)\n",
    "width = prs.slide_width * 2/5\n",
    "height = Inches(1)\n",
    "title_box = slide.shapes.add_textbox(left, top, width, height)\n",
    "title_frame = title_box.text_frame\n",
    "title_p = title_frame.add_paragraph()\n",
    "title_p.text = title_text\n",
    "title_p.font.bold = True\n",
    "title_p.font.size = Pt(38)\n",
    "title_p.font.color.rgb = RGBColor(255, 255, 255)\n",
    "title_p.alignment = PP_PARAGRAPH_ALIGNMENT.CENTER\n",
    "\n",
    "# 添加副标题文本框\n",
    "left = prs.slide_width * 3/5\n",
    "top = Inches(3)\n",
    "width = prs.slide_width * 2/5\n",
    "height = Inches(1)\n",
    "subtitle_box = slide.shapes.add_textbox(left, top, width, height)\n",
    "subtitle_frame = subtitle_box.text_frame\n",
    "subtitle_p = subtitle_frame.add_paragraph()\n",
    "subtitle_p.text = subtitle_text\n",
    "subtitle_p.font.size = Pt(22)\n",
    "subtitle_p.font.color.rgb = RGBColor(255, 255, 255)\n",
    "subtitle_p.alignment = PP_PARAGRAPH_ALIGNMENT.CENTER\n",
    "\"\"\"\n",
    "\n",
    "data_vis_template = \"\"\"\n",
    "from pptx import Presentation\n",
    "from pptx.util import Inches, Pt\n",
    "from pptx.enum.text import PP_PARAGRAPH_ALIGNMENT\n",
    "from pptx.dml.color import RGBColor\n",
    "\n",
    "# 创建新的演示文稿对象\n",
    "prs = Presentation()\n",
    "\n",
    "# 添加一个空白的幻灯片布局\n",
    "blank_slide_layout = prs.slide_layouts[6]\n",
    "slide = prs.slides.add_slide(blank_slide_layout)\n",
    "\n",
    "# 将幻灯片的背景颜色设置为黑色\n",
    "background = slide.background\n",
    "fill = background.fill\n",
    "fill.solid()\n",
    "fill.fore_color.rgb = RGBColor(0, 0, 0)\n",
    "\n",
    "# 定义占位符\n",
    "image_path = data_vis_img\n",
    "title_text = \"提升利润：在线销售与直销优化的主导地位\"\n",
    "bullet_points = \"• 在线销售在各个季度中始终领先于盈利能力，表明了强大的数字市场存在。\\n• 直销表现出波动，表明该渠道的表现变化和需要针对性改进的必要性。\"\n",
    "\n",
    "# 在幻灯片左侧添加图片占位符\n",
    "left = Inches(0.2)\n",
    "top = Inches(1.8)\n",
    "height = prs.slide_height - Inches(3)\n",
    "width = prs.slide_width * 3/5\n",
    "pic = slide.shapes.add_picture(image_path, left, top, width=width, height=height)\n",
    "\n",
    "# 添加覆盖整个宽度的标题文本\n",
    "left = Inches(0)\n",
    "top = Inches(0)\n",
    "width = prs.slide_width\n",
    "height = Inches(1)\n",
    "title_box = slide.shapes.add_textbox(left, top, width, height)\n",
    "title_frame = title_box.text_frame\n",
    "title_frame.margin_top = Inches(0.1)\n",
    "title_p = title_frame.add_paragraph()\n",
    "title_p.text = title_text\n",
    "title_p.font.bold = True\n",
    "title_p.font.size = Pt(28)\n",
    "title_p.font.color.rgb = RGBColor(255, 255, 255)\n",
    "title_p.alignment = PP_PARAGRAPH_ALIGNMENT.CENTER\n",
    "\n",
    "# 添加硬编码的“关键见解”文本和项目符号列表\n",
    "left = prs.slide_width * 2/3\n",
    "top = Inches(1.5)\n",
    "width = prs.slide_width * 1/3\n",
    "height = Inches(4.5)\n",
    "insights_box = slide.shapes.add_textbox(left, top, width, height)\n",
    "insights_frame = insights_box.text_frame\n",
    "insights_p = insights_frame.add_paragraph()\n",
    "insights_p.text = \"关键见解：\"\n",
    "insights_p.font.bold = True\n",
    "insights_p.font.size = Pt(24)\n",
    "insights_p.font.color.rgb = RGBColor(0, 128, 100)\n",
    "insights_p.alignment = PP_PARAGRAPH_ALIGNMENT.LEFT\n",
    "insights_frame.add_paragraph()\n",
    "\n",
    "\n",
    "bullet_p = insights_frame.add_paragraph()\n",
    "bullet_p.text = bullet_points\n",
    "bullet_p.font.size = Pt(12)\n",
    "bullet_p.font.color.rgb = RGBColor(255, 255, 255)\n",
    "bullet_p.line_spacing = 1.5\n",
    "\"\"\"\n",
    "\n",
    "title_text = \"鸟语乐境\"\n",
    "subtitle_text = \"2025年销售大会\"\n",
    "\n",
    "submit_message_wait_completion(assistant.id,thread,f\"使用包含的代码模板创建符合模板格式的PPTX幻灯片，但使用本消息中包含的图片、公司名称/标题和文件名/副标题：\\\n",
    "{title_template}。重要提示：在此第一张幻灯片中使用本消息中包含的图片文件作为image_path图像，并使用公司名称 {title_text} 作为title_text变量，\\\n",
    "  使用副标题文本 {subtitle_text} 作为subtitle_text变量。\\\n",
    "    接着，使用以下代码模板创建第二张幻灯片：{data_vis_template}，创建符合模板格式的PPTX幻灯片，但使用公司名称/标题和文件名/副标题：\\\n",
    "{data_vis_template}。重要提示：使用您之前在本线程中创建的第二个附图（折线图）作为data_vis_img图像，并使用您之前创建的数据可视化标题作为title_text变量，\\\n",
    "  使用您之前创建的见解项目符号列表作为bullet_points变量。将这两张幻灯片输出为.pptx文件。确保输出为两张幻灯片，每张幻灯片都符合本消息中给出的相应模板。\",\n",
    "              file_ids=[dalle_file.id, plot_file.id]\n",
    ")\n",
    "\n",
    "\n",
    "# 等待助手完成PPT创建任务\n",
    "while True:\n",
    "    try:\n",
    "        response = get_response(thread)\n",
    "        pptx_id = response.data[0].content[0].text.annotations[0].file_path.file_id\n",
    "        print(\"成功检索到 pptx_id:\", pptx_id)\n",
    "        break\n",
    "    except Exception as e:\n",
    "        print(\"您的Assistant正在努力制作幻灯片...\")\n",
    "        time.sleep(10)\n",
    "\n",
    "import io\n",
    "pptx_id = response.data[0].content[0].text.annotations[0].file_path.file_id\n",
    "ppt_file= client.files.content(pptx_id)\n",
    "file_obj = io.BytesIO(ppt_file.read())\n",
    "with open(\"销售大会.pptx\", \"wb\") as f:\n",
    "    f.write(file_obj.getbuffer())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
